<?php
include_once("config.php");


function func_bw_total_volume(){
	// Connect to server and select databse.
    global $host, $username, $password, $db_name;
    
	mysql_connect($host, $username, $password) or die("cannot connect");
	mysql_select_db("$db_name")or die("cannot select DB");

	$sql="Select sum(size) as size from logbook;";
	$result=mysql_query($sql);

	$row = mysql_fetch_array($result) or die(mysql_error());

    if (isset($row['size'])){
        echo round((($row['size'] / 1024) / 1024),1) . ' MB';
    }else{
        echo '0 MB';
    }

}

function func_bw_total_volume_periode($Start, $End){
	// Connect to server and select databse.
    global $host, $username, $password, $db_name;
    
	mysql_connect("$host", "$username", "$password")or die("cannot connect");
	mysql_select_db("$db_name")or die("cannot select DB");

	$sql="select sum(size) as size from logbook where Timestamp >= '$Start' and Timestamp <= '$End';";
    $result=mysql_query($sql);

	$row = mysql_fetch_array($result) or die(mysql_error());

    if (isset($row['size'])){
        echo round((($row['size'] / 1024) / 1024),1) . ' MB';
    }else{
        echo '0 MB';
    }
}

function func_bw_total_volume_periode_hour_graph($Start, $End){
	// Connect to server and select databse.
    global $host, $username, $password, $db_name;

	mysql_connect("$host", "$username", "$password")or die("cannot connect");
	mysql_select_db("$db_name")or die("cannot select DB");

	$sql="select hour(Timestamp) as uur, sum(size) as size from logbook where Timestamp >= '$Start' and Timestamp <= '$End' group by hour(Timestamp);";
    //echo $sql;

	$result=mysql_query($sql) or die(mysql_error());;

    $bw = array(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);

	while ($row = mysql_fetch_array($result)){
        $bw[$row['uur']] = round((($row['size'] / 1024) / 1024),1);
    }

    return $bw;
}

function func_bw_total_volume_periode_day_graph_7days($Start, $End){
	// Connect to server and select databse.
    global $host, $username, $password, $db_name;

	mysql_connect("$host", "$username", "$password")or die("cannot connect");
	mysql_select_db("$db_name")or die("cannot select DB");

	$sql="select day(Timestamp) as dag, month(Timestamp) as maand, year(Timestamp) as jaar, sum(size) as size from logbook where Timestamp >= '$Start' and Timestamp <= '$End' group by day(Timestamp);";

	$result=mysql_query($sql) or die(mysql_error());;

    //Samenstellen datums array
    //opbouw van de array
    //Size, dag, maand, jaar
    $Datum = date("Y/m/d") . " 00:00:00";
    $bw = array(array(0,date("d", DateAdd("d", -6, $Datum)), date("m", DateAdd("d", -6, $Datum)) , date("Y", DateAdd("d", -6, $Datum))),
            array(0,date("d", DateAdd("d", -5, $Datum)), date("m", DateAdd("d", -5, $Datum)) , date("Y", DateAdd("d", -5, $Datum))),
            array(0,date("d", DateAdd("d", -4, $Datum)), date("m", DateAdd("d", -4, $Datum)) , date("Y", DateAdd("d", -4, $Datum))),
            array(0,date("d", DateAdd("d", -3, $Datum)), date("m", DateAdd("d", -3, $Datum)) , date("Y", DateAdd("d", -3, $Datum))),
            array(0,date("d", DateAdd("d", -2, $Datum)), date("m", DateAdd("d", -2, $Datum)) , date("Y", DateAdd("d", -2, $Datum))),
            array(0,date("d", DateAdd("d", -1, $Datum)), date("m", DateAdd("d", -1, $Datum)) , date("Y", DateAdd("d", -1, $Datum))),
            array(0,date("d"), date("m") , date("Y")),
            );
    $Datum = date("Y/m/d", $Datum) . ' 00:00:00';
    $Datum = DateAdd("d", 1, $Datum);
        

    $teller = 0;
	while ($row = mysql_fetch_array($result)){
        $i = 0;
        while ($i < 7){
            if ($row['dag'] == $bw[$i][1]){
                if ($row['maand'] == $bw[$i][2]){
                    if ($row['jaar'] == $bw[$i][3]){
                        $bw[$i][0] = round((($row['size'] / 1024) / 1024),1);
                    }
                }
            }
            $i++;
        }
    }

    return $bw;
}

function func_bw_total_volume_month_day_graph($Start, $End){
	// Connect to server and select databse.
    global $host, $username, $password, $db_name;

	mysql_connect("$host", "$username", "$password")or die("cannot connect");
	mysql_select_db("$db_name")or die("cannot select DB");

	$sql="select day(Timestamp) as dag, month(Timestamp) as maand, year(Timestamp) as jaar, sum(size) as size from logbook where Timestamp >= '$Start' and Timestamp <= '$End' group by day(Timestamp);";
        
	$result=mysql_query($sql) or die(mysql_error());;

    //Samenstellen datums array
    //opbouw van de array
    //Size, dag, maand, jaar
    $Datum = date("Y/m/") . "1 00:00:00";

    $bw = array(array(0,date("d", $Datum), date("m", $Datum) , date("Y", $Datum)),
            array(0,date("d", DateAdd("d", 1, $Datum)), date("m", DateAdd("d", 1, $Datum)) , date("Y", DateAdd("d", 1, $Datum))),
            array(0,date("d", DateAdd("d", 2, $Datum)), date("m", DateAdd("d", 2, $Datum)) , date("Y", DateAdd("d", 2, $Datum))),
            array(0,date("d", DateAdd("d", 3, $Datum)), date("m", DateAdd("d", 3, $Datum)) , date("Y", DateAdd("d", 3, $Datum))),
            array(0,date("d", DateAdd("d", 4, $Datum)), date("m", DateAdd("d", 4, $Datum)) , date("Y", DateAdd("d", 4, $Datum))),
            array(0,date("d", DateAdd("d", 5, $Datum)), date("m", DateAdd("d", 5, $Datum)) , date("Y", DateAdd("d", 5, $Datum))),
            array(0,date("d", DateAdd("d", 6, $Datum)), date("m", DateAdd("d", 6, $Datum)) , date("Y", DateAdd("d", 6, $Datum))),
            array(0,date("d", DateAdd("d", 7, $Datum)), date("m", DateAdd("d", 7, $Datum)) , date("Y", DateAdd("d", 7, $Datum))),
            array(0,date("d", DateAdd("d", 8, $Datum)), date("m", DateAdd("d", 8, $Datum)) , date("Y", DateAdd("d", 8, $Datum))),
            array(0,date("d", DateAdd("d", 9, $Datum)), date("m", DateAdd("d", 9, $Datum)) , date("Y", DateAdd("d", 9, $Datum))),
            array(0,date("d", DateAdd("d",10, $Datum)), date("m", DateAdd("d",10, $Datum)) , date("Y", DateAdd("d",10, $Datum))),
            array(0,date("d", DateAdd("d",11, $Datum)), date("m", DateAdd("d",11, $Datum)) , date("Y", DateAdd("d",11, $Datum))),
            array(0,date("d", DateAdd("d",12, $Datum)), date("m", DateAdd("d",12, $Datum)) , date("Y", DateAdd("d",12, $Datum))),
            array(0,date("d", DateAdd("d",13, $Datum)), date("m", DateAdd("d",13, $Datum)) , date("Y", DateAdd("d",13, $Datum))),
            array(0,date("d", DateAdd("d",14, $Datum)), date("m", DateAdd("d",14, $Datum)) , date("Y", DateAdd("d",14, $Datum))),
            array(0,date("d", DateAdd("d",15, $Datum)), date("m", DateAdd("d",15, $Datum)) , date("Y", DateAdd("d",15, $Datum))),
            array(0,date("d", DateAdd("d",16, $Datum)), date("m", DateAdd("d",16, $Datum)) , date("Y", DateAdd("d",16, $Datum))),
            array(0,date("d", DateAdd("d",17, $Datum)), date("m", DateAdd("d",17, $Datum)) , date("Y", DateAdd("d",17, $Datum))),
            array(0,date("d", DateAdd("d",18, $Datum)), date("m", DateAdd("d",18, $Datum)) , date("Y", DateAdd("d",18, $Datum))),
            array(0,date("d", DateAdd("d",19, $Datum)), date("m", DateAdd("d",19, $Datum)) , date("Y", DateAdd("d",19, $Datum))),
            array(0,date("d", DateAdd("d",20, $Datum)), date("m", DateAdd("d",20, $Datum)) , date("Y", DateAdd("d",20, $Datum))),
            array(0,date("d", DateAdd("d",21, $Datum)), date("m", DateAdd("d",21, $Datum)) , date("Y", DateAdd("d",21, $Datum))),
            array(0,date("d", DateAdd("d",22, $Datum)), date("m", DateAdd("d",22, $Datum)) , date("Y", DateAdd("d",22, $Datum))),
            array(0,date("d", DateAdd("d",23, $Datum)), date("m", DateAdd("d",23, $Datum)) , date("Y", DateAdd("d",23, $Datum))),
            array(0,date("d", DateAdd("d",24, $Datum)), date("m", DateAdd("d",24, $Datum)) , date("Y", DateAdd("d",24, $Datum))),
            array(0,date("d", DateAdd("d",25, $Datum)), date("m", DateAdd("d",25, $Datum)) , date("Y", DateAdd("d",25, $Datum))),
            array(0,date("d", DateAdd("d",26, $Datum)), date("m", DateAdd("d",26, $Datum)) , date("Y", DateAdd("d",26, $Datum))),
            array(0,date("d", DateAdd("d",27, $Datum)), date("m", DateAdd("d",27, $Datum)) , date("Y", DateAdd("d",27, $Datum))),
            array(0,date("d", DateAdd("d",28, $Datum)), date("m", DateAdd("d",28, $Datum)) , date("Y", DateAdd("d",28, $Datum))),
            array(0,date("d", DateAdd("d",29, $Datum)), date("m", DateAdd("d",29, $Datum)) , date("Y", DateAdd("d",29, $Datum))),
            array(0,date("d", DateAdd("d",30, $Datum)), date("m", DateAdd("d",30, $Datum)) , date("Y", DateAdd("d",30, $Datum))),
            array(0,date("d", DateAdd("d",31, $Datum)), date("m", DateAdd("d",31, $Datum)) , date("Y", DateAdd("d",31, $Datum)))
            );

            //print_r($bw);

    $teller = 0;
	while ($row = mysql_fetch_array($result)){
        $i = 0;
        while ($i < 32){
            if ($row['dag'] == $bw[$i][1]){
                if ($row['maand'] == $bw[$i][2]){
                    if ($row['jaar'] == $bw[$i][3]){
                        $bw[$i][0] = round((($row['size'] / 1024) / 1024),1);
                    }
                }
            }
            $i++;
        }
    }

    return $bw;
}

function func_bw_total_volume_prev_month_day_graph($Start, $End){
	// Connect to server and select databse.
    global $host, $username, $password, $db_name;

	mysql_connect("$host", "$username", "$password")or die("cannot connect");
	mysql_select_db("$db_name")or die("cannot select DB");

	$sql="select day(Timestamp) as dag, month(Timestamp) as maand, year(Timestamp) as jaar, sum(size) as size from logbook where Timestamp >= '$Start' and Timestamp <= '$End' group by day(Timestamp);";
    //echo $sql;

	$result=mysql_query($sql) or die(mysql_error());;

    //Samenstellen datums array
    //opbouw van de array
    //Size, dag, maand, jaar
    $Datum = date("Y/m/") . "1 00:00:00";
    $Datum = DateAdd("m", -1, $Datum);
    $Datum = date("Y/m/d", $Datum) . ' 00:00:00' ;
    //echo $Datum ."/";

  
    $bw = array(array(0,date("d", DateAdd("d", 0, $Datum)), date("m", DateAdd("d", 0, $Datum)) , date("Y", DateAdd("d", 0, $Datum))),
            array(0,date("d", DateAdd("d", 1, $Datum)), date("m", DateAdd("d", 1, $Datum)) , date("Y", DateAdd("d", 1, $Datum))),
            array(0,date("d", DateAdd("d", 2, $Datum)), date("m", DateAdd("d", 2, $Datum)) , date("Y", DateAdd("d", 2, $Datum))),
            array(0,date("d", DateAdd("d", 3, $Datum)), date("m", DateAdd("d", 3, $Datum)) , date("Y", DateAdd("d", 3, $Datum))),
            array(0,date("d", DateAdd("d", 4, $Datum)), date("m", DateAdd("d", 4, $Datum)) , date("Y", DateAdd("d", 4, $Datum))),
            array(0,date("d", DateAdd("d", 5, $Datum)), date("m", DateAdd("d", 5, $Datum)) , date("Y", DateAdd("d", 5, $Datum))),
            array(0,date("d", DateAdd("d", 6, $Datum)), date("m", DateAdd("d", 6, $Datum)) , date("Y", DateAdd("d", 6, $Datum))),
            array(0,date("d", DateAdd("d", 7, $Datum)), date("m", DateAdd("d", 7, $Datum)) , date("Y", DateAdd("d", 7, $Datum))),
            array(0,date("d", DateAdd("d", 8, $Datum)), date("m", DateAdd("d", 8, $Datum)) , date("Y", DateAdd("d", 8, $Datum))),
            array(0,date("d", DateAdd("d", 9, $Datum)), date("m", DateAdd("d", 9, $Datum)) , date("Y", DateAdd("d", 9, $Datum))),
            array(0,date("d", DateAdd("d",10, $Datum)), date("m", DateAdd("d",10, $Datum)) , date("Y", DateAdd("d",10, $Datum))),
            array(0,date("d", DateAdd("d",11, $Datum)), date("m", DateAdd("d",11, $Datum)) , date("Y", DateAdd("d",11, $Datum))),
            array(0,date("d", DateAdd("d",12, $Datum)), date("m", DateAdd("d",12, $Datum)) , date("Y", DateAdd("d",12, $Datum))),
            array(0,date("d", DateAdd("d",13, $Datum)), date("m", DateAdd("d",13, $Datum)) , date("Y", DateAdd("d",13, $Datum))),
            array(0,date("d", DateAdd("d",14, $Datum)), date("m", DateAdd("d",14, $Datum)) , date("Y", DateAdd("d",14, $Datum))),
            array(0,date("d", DateAdd("d",15, $Datum)), date("m", DateAdd("d",15, $Datum)) , date("Y", DateAdd("d",15, $Datum))),
            array(0,date("d", DateAdd("d",16, $Datum)), date("m", DateAdd("d",16, $Datum)) , date("Y", DateAdd("d",16, $Datum))),
            array(0,date("d", DateAdd("d",17, $Datum)), date("m", DateAdd("d",17, $Datum)) , date("Y", DateAdd("d",17, $Datum))),
            array(0,date("d", DateAdd("d",18, $Datum)), date("m", DateAdd("d",18, $Datum)) , date("Y", DateAdd("d",18, $Datum))),
            array(0,date("d", DateAdd("d",19, $Datum)), date("m", DateAdd("d",19, $Datum)) , date("Y", DateAdd("d",19, $Datum))),
            array(0,date("d", DateAdd("d",20, $Datum)), date("m", DateAdd("d",20, $Datum)) , date("Y", DateAdd("d",20, $Datum))),
            array(0,date("d", DateAdd("d",21, $Datum)), date("m", DateAdd("d",21, $Datum)) , date("Y", DateAdd("d",21, $Datum))),
            array(0,date("d", DateAdd("d",22, $Datum)), date("m", DateAdd("d",22, $Datum)) , date("Y", DateAdd("d",22, $Datum))),
            array(0,date("d", DateAdd("d",23, $Datum)), date("m", DateAdd("d",23, $Datum)) , date("Y", DateAdd("d",23, $Datum))),
            array(0,date("d", DateAdd("d",24, $Datum)), date("m", DateAdd("d",24, $Datum)) , date("Y", DateAdd("d",24, $Datum))),
            array(0,date("d", DateAdd("d",25, $Datum)), date("m", DateAdd("d",25, $Datum)) , date("Y", DateAdd("d",25, $Datum))),
            array(0,date("d", DateAdd("d",26, $Datum)), date("m", DateAdd("d",26, $Datum)) , date("Y", DateAdd("d",26, $Datum))),
            array(0,date("d", DateAdd("d",27, $Datum)), date("m", DateAdd("d",27, $Datum)) , date("Y", DateAdd("d",27, $Datum))),
            array(0,date("d", DateAdd("d",28, $Datum)), date("m", DateAdd("d",28, $Datum)) , date("Y", DateAdd("d",28, $Datum))),
            array(0,date("d", DateAdd("d",29, $Datum)), date("m", DateAdd("d",29, $Datum)) , date("Y", DateAdd("d",29, $Datum))),
            array(0,date("d", DateAdd("d",30, $Datum)), date("m", DateAdd("d",30, $Datum)) , date("Y", DateAdd("d",30, $Datum))),
            array(0,date("d", DateAdd("d",31, $Datum)), date("m", DateAdd("d",31, $Datum)) , date("Y", DateAdd("d",31, $Datum)))
            );

            //print_r($bw);

    $teller = 0;


    while ($row = mysql_fetch_array($result)){
        $i = 0;
        while ($i < 33){
            if ($row['dag'] == $bw[$i][1]){
                if ($row['maand'] == $bw[$i][2]){
                    if ($row['jaar'] == $bw[$i][3]){
                        $bw[$i][0] = round((($row['size'] / 1024) / 1024),1);
                    }
                }
            }
            
            $i++;
        }
    }

    return $bw;
}

?>

<!--
Totaal gesurft volume per periode per client:
select client, sum(size) as size from logbook where datum >= 'yyyy/MM/dd 00:00:00' and datum <= 'yyyy/MM/dd 23:59:59' group by client;
Totaal gesurft volume per periode per ident:
select Ident, sum(size) as size from logbook where datum >= 'yyyy/MM/dd 00:00:00' and datum <= 'yyyy/MM/dd 23:59:59' group by Ident;
Totaal gesurfd volume per maand:
select month(timestamp) as MM, year(timestamp) as YY, sum(size) from logbook group by month(timestamp), year(timestamp);
Totaal gesurft volume per maand per client:
select month(timestamp) as MM, year(timestamp) as YY, sum(size), client from logbook group by client, month(timestamp), year(timestamp);
Totaal gesurft volume per maand per Ident:
select month(timestamp) as MM, year(timestamp) as YY, sum(size), Ident from logbook group by Ident, month(timestamp), year(timestamp);
Totaal gesurft volume per dag:
select day(timestamp) as DD, month(timestamp) as MM, year(timestamp) as YY, sum(size) from logbook group by client, day(timestamp), month(timestamp), year(timestamp);
Totaal gesurft volume per dag en per client:
select day(timestamp) as DD, month(timestamp) as MM, year(timestamp) as YY, client,sum(size) from logbook group by client, day(timestamp), month(timestamp), year(timestamp);
Totaal gesurft volume per dag en per ident:
select day(timestamp) as DD, month(timestamp) as MM, year(timestamp) as YY, Ident,sum(size) from logbook group by Ident, day(timestamp), month(timestamp), year(timestamp);
-->